﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Xml.Linq;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Linq;
using SPQueryToSql.Extensions;
using Schema = SPQueryToSql.CAML;

namespace SPQueryToSql.ApplicationObjects.CAML
{
    internal class Query : BaseOperator
    {
        public Query(SPQuery query, SPList list)
        {
            PageIndex = 0;
            var queryStr = string.Format("<View><ViewFields>{0}</ViewFields><Query>{1}</Query><QueryOptions>{2}</QueryOptions></View>",
                query.ViewFields, query.Query, string.Empty);
            Init(queryStr, list);
            if (query.Folder != null)
                RootFolder = query.Folder.ToString();
        }

        internal Query(string selectCommand, SPList list)
        {
            PageIndex = 0;
            Init(selectCommand, list);
        }

        ///<summary>
        /// Size of resultset' page
        ///</summary>
        public uint PageSize;
        /// <summary>
        /// Zero-based page' index
        /// </summary>
        public uint PageIndex;
        public string RootFolder;
        public Where Where;
        public ViewFields ViewFields;
        public GroupBy GroupBy;
        public OrderBy OrderBy;
        public Guid ListId;
        public QueryOptions QueryOptions;
        public List<FieldInfo> ListFields;
        public List<ContentTypeInfo> ContentTypes;

        private void Init(string xml, SPList list)
        {
            ContentTypes = list.ContentTypes
                .Cast<SPContentType>()
                .Select(x => new ContentTypeInfo(x, list.ParentWeb.UICulture))
                .ToList();
            ListFields = list.GetFields().ToList();
            ListId = list.ID;

            var queryXml = XElement.Parse(xml);
            var queryElement = queryXml.Element(Schema.Query);
            if (queryElement == null) return;
            var where = queryElement.Element(Schema.Where);
            if (where != null)
                Where = new Where(where, ListFields);
            var viewFields = queryXml.Element(Schema.ViewFields);
            if (viewFields != null)
                ViewFields = new ViewFields(viewFields, ListFields);
            var groupBy = queryXml.Element(Schema.GroupBy);
            if (groupBy != null)
                GroupBy = new GroupBy(groupBy);
            var orderBy = queryXml.Element(Schema.OrderBy);
            if (orderBy != null)
                OrderBy = new OrderBy(orderBy);
            var queryOptions = queryXml.Element(Schema.QueryOptions);
            if (queryOptions != null)
                QueryOptions = new QueryOptions(queryOptions);
        }

        public override string ToString()
        {
            var sb = new StringBuilder();
            if (ViewFields != null)
            {
                sb.Append(ViewFields.ToString());
                sb.Append(Environment.NewLine);
            }
            if (Where != null)
            {
                sb.Append(Where.ToString());
                sb.Append(Environment.NewLine);
            }
            if (GroupBy != null)
            {
                sb.Append(GroupBy.ToString());
                sb.Append(Environment.NewLine);
            }
            if (OrderBy != null)
            {
                sb.Append(OrderBy.ToString());
                sb.Append(Environment.NewLine);
            }
            return sb.ToString();
        }

        public string ToSQL()
        {
            return ToSQL(SelectMode.Fields);
        }

        internal string ToSQL(SelectMode mode)
        {
            var sql = new StringBuilder();
            var targetColumns = (ViewFields == null
                                     ? ListFields
                                     : ListFields
                                           .Where(f => ViewFields.Fields.Any(x => x.ID == f.Id)))
                .Select(f => string.Format("[{0}]", f.InternalName))
                .ToArray();
            var targetFields = (ViewFields == null
                                    ? ListFields
                                    : ListFields
                                          .Where(f => ViewFields.Fields.Any(x => x.ID == f.Id)))
                .ToList();
            var usedFields = Where == null
                                 ? ListFields
                                 : Where.Operators
                                       .SelectMany(o => o.UsedFields)
                                       .Union(targetFields)
                                       .Where(f => f != null)
                                       .Distinct()
                                       .ToList();
            if (!usedFields.Any(f => f.InternalName.Equals("Id", StringComparison.InvariantCultureIgnoreCase)))
                usedFields.Add(ListFields.FirstOrDefault(f => f.InternalName.Equals("Id", StringComparison.InvariantCultureIgnoreCase)));
            var lookupFields = usedFields
                .Where(f => !string.IsNullOrEmpty(f.LookupFieldColumn));
            var userFields = usedFields
                .Where(f => f.Type == SPFieldType.User);

            var lookupTables = new Dictionary<FieldInfo, string>();
            var userTables = new Dictionary<FieldInfo, string>();

            var internalFields = new List<string>();
            foreach (var field in usedFields)
            {
                switch (field.Type)
                {
                    case SPFieldType.User:
                        internalFields.Add(string.Format("[User_{0}].Val [{1}]", field.InternalName, field.InternalName));
                        break;
                    case SPFieldType.Lookup:
                        internalFields.Add(string.Format("[Lookup_{0}].Val [{1}]", field.InternalName, field.InternalName));
                        break;
                    default:
                        internalFields.Add(string.Format("UserData_{0}.[{1}] [{2}]", field.RowOrdinal, field.ColumnName, field.InternalName));
                        break;
                }
            }

            #region LookupTables
            foreach (var lookup in lookupFields)
            {
                if (lookup.IsMulti)
                {
                    lookupTables.Add(lookup, string.Format(SQL.LookupMultiFormat,
                                                           lookup.LookupListId.ToString("B"),
                                                           lookup.Id.ToString("B"),
                                                           lookup.LookupFieldColumn,
                                                           lookup.InternalName,
                                                           lookup.RowOrdinal));
                }
                else
                {
                    lookupTables.Add(lookup, string.Format(SQL.LookupSingleFormat,
                                                           lookup.LookupListId.ToString("B"),
                                                           lookup.LookupFieldColumn,
                                                           lookup.InternalName,
                                                           lookup.ColumnName,
                                                           lookup.RowOrdinal));
                }
            }
            #endregion
            #region UserTables
            foreach (var field in userFields)
            {
                if (field.IsMulti)
                {
                    userTables.Add(field, string.Format(SQL.UserMultiFormat,
                                                        field.SiteId.ToString("B"),
                                                        field.Id.ToString("B"),
                                                        field.InternalName,
                                                        field.RowOrdinal));
                }
                else
                {
                    userTables.Add(field, string.Format(SQL.UserSingleFormat,
                                                        field.SiteId.ToString("B"),
                                                        field.InternalName,
                                                        field.RowOrdinal));
                }
            }
            #endregion
            sql.AppendFormat(@"WITH ResultTable AS (SELECT ROW_NUMBER() OVER(ORDER BY {1}) AS ROWNUMBER, Docs.[Type] [FSObjType], tp_ModerationStatus [ModerationStatus], CASE WHEN DATALENGTH(Docs.DirName) = 0 THEN Docs.LeafName WHEN DATALENGTH(Docs.LeafName) = 0 THEN Docs.DirName ELSE Docs.DirName + N'/' + Docs.LeafName END  AS FileDirRef, {0} FROM",
                string.Join(", ", internalFields.ToArray()), OrderBy==null ? @"tp_Ordering" : OrderBy.ToRowNumberSql(ListFields));

            var rows = userFields.Select(f => f.RowOrdinal).Distinct();
            if (rows.Count() == 1)
            {
                sql.AppendFormat(@"[dbo].[AllUserData] UserData_{0} (NOLOCK) ", rows.Min());
            }
            else
            {
                sql.Append(string.Join(Environment.NewLine, rows.Select(r => string.Format(@"[dbo].[AllUserData]) UserData_{0} (NOLOCK)", r)).ToArray()));
            }
            sql.AppendFormat(
                @"INNER JOIN [dbo].[AllDocs] Docs (NOLOCK) ON Docs.Id = UserData_{1}.tp_DocId {2} {3} WHERE Docs.ListId = UserData_{1}.tp_ListId AND UserData_{1}.tp_DeleteTransactionId = 0x AND UserData_{1}.tp_IsCurrent = 1 AND UserData_{1}.tp_ListId = '{0}'",
                ListId.ToString("B"), rows.Min(), string.Join(Environment.NewLine, userTables.Select(x => x.Value).ToArray()),
                string.Join(Environment.NewLine, lookupTables.Select(x => x.Value).ToArray()));
            var r2 = rows.ToArray();
            for (var i = 0; i < r2.Length - 1; i++)
            {
                var row = r2[i];
                var nextRow = r2[i + 1];
                sql.AppendFormat(" AND UserData_{0}.Id=UserData_{1}.Id AND UserData_{0}.ListId=UserData_{1}.ListId", row, nextRow);
            }
            foreach (var row in rows)
            {
                sql.AppendFormat(" AND UserData_{0}.tp_RowOrdinal = {0}", row);
            }
            sql.AppendFormat(") ");
            switch (mode)
            {
                case SelectMode.Count:
                    sql.Append(@"SELECT COUNT(*) FROM ResultTable");
                    break;
                case SelectMode.RandomId:
                    sql.Append(@"SELECT TOP 1 [ID] FROM ResultTable");
                    break;
                case SelectMode.Fields:
                    sql.AppendFormat(@"SELECT [FileDirRef], {0} FROM ResultTable", string.Join(", ", targetColumns));
                    break;
            }
            sql.AppendFormat(" WHERE {0}", string.IsNullOrEmpty(OperatorsSqlPredicate) ? "1=1" : OperatorsSqlPredicate);
            
            if (QueryOptions != null)
            {
                if (!string.IsNullOrEmpty(RootFolder))
                {
                    if (QueryOptions.ViewAttributes.Scope == "Recursive")
                        sql.AppendFormat(" AND ([RootFolder] LIKE '%{0}/%' AND RootFolder IS NOT LIKE '%{1}/%/%')", RootFolder, RootFolder);
                    else if (QueryOptions.ViewAttributes.Scope == "RecursiveAll")
                        sql.AppendFormat(" AND ([RootFolder] LIKE '%{0}/%')", RootFolder);
                }
                if (QueryOptions.ViewAttributes.Scope == "FilesOnly")
                    sql.Append(" AND ([FSObjType] = 0)");
                if (QueryOptions.ViewAttributes.ModerationType == "HideUnapproved")
                    sql.Append(" AND ([ModerationStatus] = 0)");
                if (QueryOptions.RowLimit != null)
                {
                    if (PageSize == 0) PageSize = Convert.ToUInt32(QueryOptions.RowLimit);
                    sql.AppendFormat(" AND ([ROWNUMBER] BETWEEN {0} AND {1})", PageIndex*PageSize,
                                     (PageIndex + 1)*PageSize);
                }
            }
            switch (mode)
            {
                case SelectMode.RandomId:
                    sql.Append("ORDER BY NEWID()");
                    break;
                case SelectMode.Fields:
                    if (OrderBy != null)
                    {
                        var sortFields = ListFields
                            .Join(ViewFields.Fields, lf => lf.InternalName, vf => vf.Name, (lf, vf) => vf)
                            .Select(f => f.Name + (f.Ascending == false ? " DESC" : " ASC"))
                            .ToArray();
                        if (sortFields.Any())
                        {
                            sql.AppendFormat(" ORDER BY {0}", string.Join(", ", sortFields));
                        }
                    }
                    break;
            }
            return sql.ToString();
        }

        private string OperatorsSqlPredicate
        {
            get
            {
                var sql = new StringBuilder(10);
                if (Where == null) return sql.ToString();
                foreach (var op in Where.Operators)
                {
                    try
                    {
                        sql.Append(op.ToSql(this) + Environment.NewLine);
                    }
                    catch (Exception ex)
                    {
                        sql.Append(ex.Message);
                    }
                }
                return sql.ToString();
            }
        }

        internal bool Any()
        {
            return Count() > 0;
        }

        internal int Count()
        {
            var web = SPContext.Current.Web;
            var sql = ToSQL(SelectMode.Count);
            var res = 0;
            var siteId = web.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(
                () =>
                {
                    using (var site = new SPSite(siteId))
                    {
                        var conStr = site.ContentDatabase.DatabaseConnectionString;
                        using (var connection = new SqlConnection(conStr))
                        {
                            connection.Open();
                            var cmd = new SqlCommand(sql)
                            {
                                CommandType = CommandType.Text,
                                Connection = connection
                            };
                            var dbRes = cmd.ExecuteScalar();
                            res = dbRes == null ? 0 : (int)dbRes;
                            connection.Close();
                        }
                    }
                });
            return res;
        }

        internal int RandomId()
        {
            var web = SPContext.Current.Web;
            var sql = ToSQL(SelectMode.RandomId);
            var res = 0;
            var siteId = web.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(
                () =>
                {
                    using (var site = new SPSite(siteId))
                    {
                        var conStr = site.ContentDatabase.DatabaseConnectionString;
                        using (var connection = new SqlConnection(conStr))
                        {
                            connection.Open();
                            var cmd = new SqlCommand(sql)
                            {
                                CommandType = CommandType.Text,
                                Connection = connection
                            };
                            var dbRes = cmd.ExecuteScalar();
                            res = dbRes == null ? 0 : (int)dbRes;
                            connection.Close();
                        }
                    }
                });
            return res;
        }

        internal DataTable ToDataTable()
        {
            var web = SPContext.Current.Web;
            var sql = ToSQL(SelectMode.RandomId);
            var res = new DataTable();
            var siteId = web.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(
                () =>
                {
                    using (var site = new SPSite(siteId))
                    {
                        var conStr = site.ContentDatabase.DatabaseConnectionString;
                        using (var connection = new SqlConnection(conStr))
                        {
                            connection.Open();
                            var cmd = new SqlCommand(sql)
                            {
                                CommandType = CommandType.Text,
                                Connection = connection
                            };
                            using (var dbReader = cmd.ExecuteReader())
                            {
                                res.Load(dbReader);
                                connection.Close();
                            }

                        }
                    }
                });
            return res;
        }

        internal IEnumerable<T> ToEntityList<T>()
        {
            var res = new List<T>();
            var web = SPContext.Current.Web;
            var list = web.Lists[ListId];
            var table = list.ToDataTable();
            foreach (DataRow row in table.Rows)
            {
                // Текущий тип
                var objType = typeof(T);
                var entity = (T)Activator.CreateInstance(objType);
                // Получаем свойства текущего объекта
                var properties = objType.GetProperties();
                foreach (var property in properties)
                {
                    // Получаем атрибуты типа ColumnAttribute
                    var attributes = property.GetCustomAttributes(typeof(ColumnAttribute), false);
                    foreach (ColumnAttribute att in attributes)
                    {
                        // Берем поле для хранения значения, 
                        // указанное в атрибуте ColumnAttribute
                        var field = objType.GetField(att.Storage,
                            BindingFlags.NonPublic | BindingFlags.Instance);
                        // Если такого поля в классе нет, то просматриваем базовые классы
                        while (field == null)
                        {
                            objType = objType.BaseType;
                            if (objType == null) break;
                            field = objType.GetField(att.Storage,
                                BindingFlags.NonPublic | BindingFlags.Instance);
                        }
                        if (field != null)
                        {

                            if (att.IsLookupId || att.IsLookupValue)
                            {
                                try
                                {
                                    var fv = new SPFieldLookupValue((row[att.Name] ?? string.Empty).ToString());
                                    if (att.IsLookupId)
                                    {
                                        field.SetValue(entity, fv.LookupId);
                                    }
                                    else
                                    {
                                        field.SetValue(entity, fv.LookupValue);
                                    }
                                }
                                catch (ArgumentException) // Если Lookup поле равно null
                                {
                                    field.SetValue(entity, row[att.Name]);
                                }
                            }
                            else
                            {
                                var val = row[att.Name];
                                if (val != null)
                                {
                                    // Остальные поля записываем как есть
                                    field.SetValue(entity, row[att.Name]);
                                }
                            }
                        }
                    }
                }
                res.Add(entity);
            }
            return res;
        }
    }
}
